NULL Handling Using IFNULL, COALESCE, and NULLIF in MySQL

Course: MySQL Training

Topic: NULL Handling Using IFNULL(), COALESCE(), and NULLIF()

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to NULL Handling in MySQL

NULL handling is an important concept in MySQL because database tables frequently contain missing, unknown, unavailable, or inapplicable values. When a column contains NULL, normal comparisons and calculations may produce unexpected results if NULL is not handled correctly.

MySQL provides several functions to manage NULL values in queries. Three particularly useful functions are IFNULL(), COALESCE(), and NULLIF(). These functions help developers replace missing values, select the first available value from multiple options, and convert specific values into NULL when necessary.

For example, an employee table may contain a NULL value in the bonus column because the employee's bonus has not been assigned. A report can use IFNULL() to display zero instead of NULL. A customer table may contain multiple contact fields, and COALESCE() can return the first available phone number. NULLIF() can help convert a zero denominator into NULL before performing a division.

Understanding these functions makes SQL queries easier to read, improves report presentation, and helps prevent errors in calculations and data analysis.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand the meaning and behavior of NULL values in MySQL.
• Differentiate NULL from zero, an empty string, and a blank space.
• Use IFNULL() to replace a NULL value with a specified alternative.
• Use COALESCE() to return the first non-NULL expression from a list.
• Use NULLIF() to return NULL when two expressions are equal.
• Apply NULL-handling functions in SELECT queries, calculations, reports, and data-cleaning tasks.
• Choose the appropriate function for different database requirements.
• Identify common mistakes when working with NULL values.

3. Understanding NULL in MySQL

3.1 What Is NULL?

NULL represents a missing, unknown, or unavailable value. It does not represent a particular numeric value or a known text value. For example, if a customer has not provided an email address, the email column may contain NULL.

SELECT NULL AS missing_value;

The result is NULL because the query explicitly returns a NULL value.

3.2 NULL Versus Zero and Empty Strings

NULL, zero, and an empty string are different values and should not be treated as interchangeable.

For example, a bonus of zero may mean that an employee received no bonus, whereas a NULL bonus may mean that the bonus information has not yet been entered.

SELECT
    NULL AS null_value,
    0 AS zero_value,
    '' AS empty_string;

3.3 Why NULL Requires Special Handling

In SQL, NULL represents an unknown value. Therefore, ordinary equality comparisons with NULL do not behave like comparisons with normal values.

SELECT NULL = NULL AS comparison_result;

The result is NULL rather than TRUE. Use IS NULL to find missing values and IS NOT NULL to find values that are present.

SELECT *
FROM employees
WHERE bonus IS NULL;

SELECT *
FROM employees
WHERE bonus IS NOT NULL;

A condition such as bonus = NULL is incorrect for checking missing values. Use bonus IS NULL instead.

4. Introduction to IFNULL(), COALESCE(), and NULLIF()

All three functions are used when handling NULL values, but they serve different purposes.

5. IFNULL() Function in MySQL

5.1 What Is IFNULL()?

IFNULL() is a MySQL function that checks whether the first expression is NULL. If the first expression is not NULL, the function returns that expression. If it is NULL, the function returns the second expression.

This function is particularly useful when displaying reports or performing calculations where a missing value needs a suitable alternative.

5.2 Syntax of IFNULL()

IFNULL(expression, alternative_value)

• expression: The value or column to check.
• alternative_value: The value returned if the first expression is NULL.

5.3 Simple Examples

Example 1: Replace NULL with zero

SELECT IFNULL(NULL, 0) AS result;

Output: 0

Example 2: Keep a non-NULL value

SELECT IFNULL(500, 0) AS result;

Output: 500

Example 3: Replace NULL with text

SELECT IFNULL(NULL, 'Not Available') AS status;

Output: Not Available

Example 4: Preserve an empty string

SELECT IFNULL('', 'Not Available') AS contact_email;

The result is an empty string because an empty string is not NULL. If empty strings should also be replaced, use an additional expression such as NULLIF() or a CASE expression.

5.4 Using IFNULL() with a Table

Suppose an organization stores employee information in an employees table.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    salary DECIMAL(10,2),
    bonus DECIMAL(10,2)
);

INSERT INTO employees
(employee_id, employee_name, salary, bonus)
VALUES
(1, 'Aarav', 45000, 5000),
(2, 'Priya', 52000, NULL),
(3, 'Rohan', 40000, 0),
(4, 'Neha', 60000, NULL);

The table contains four employees. Priya and Neha have NULL bonus values, while Rohan has a known bonus of zero.

To display zero for missing bonuses, use:

SELECT
    employee_id,
    employee_name,
    salary,
    IFNULL(bonus, 0) AS displayed_bonus
FROM employees;

For Priya and Neha, the displayed_bonus column returns zero. Aarav's bonus remains 5000, and Rohan's bonus remains zero.

Important: IFNULL() changes the value returned by the query. It does not permanently update the original table.

5.5 IFNULL() in Calculations

NULL values can affect arithmetic expressions. For example, adding a salary to a NULL bonus normally produces NULL.

SELECT
    employee_name,
    salary + bonus AS total_pay
FROM employees;

For employees with NULL bonuses, the total_pay expression returns NULL. To treat missing bonuses as zero for this calculation, use:

SELECT
    employee_name,
    salary + IFNULL(bonus, 0) AS total_pay
FROM employees;

This produces a numeric total for each employee. Use this approach only when a missing bonus should logically be treated as zero for the particular calculation.

5.6 IFNULL() in Reports

Reports often require readable output instead of blank or NULL values.

SELECT
    employee_name,
    IFNULL(bonus, 0) AS bonus_amount,
    salary + IFNULL(bonus, 0) AS total_compensation
FROM employees;

This query produces a report with a bonus amount and total compensation for every employee. The underlying database values remain unchanged.

6. COALESCE() Function in MySQL

6.1 What Is COALESCE()?

COALESCE() returns the first expression in its argument list that is not NULL. It evaluates the supplied alternatives in order and returns the first available value. If every expression is NULL, the result is NULL.

COALESCE() is useful when data can be obtained from several alternative columns, such as a primary phone number, a secondary phone number, or an emergency contact number.

6.2 Syntax of COALESCE()

COALESCE(expression1, expression2, expression3, ...)

• The function accepts two or more expressions.
• Expressions are checked in the order supplied.
• The first non-NULL expression is returned.
• If all expressions are NULL, the function returns NULL.

6.3 Simple Examples

Example 1: First value is not NULL

SELECT COALESCE('Available', 'Backup', 'Default') AS result;

Output: Available

Example 2: First value is NULL

SELECT COALESCE(NULL, 'Backup', 'Default') AS result;

Output: Backup

Example 3: Multiple NULL values

SELECT COALESCE(NULL, NULL, 100) AS result;

Output: 100

Example 4: All values are NULL

SELECT COALESCE(NULL, NULL, NULL) AS result;

Output: NULL

6.4 Using COALESCE() with Contact Information

Consider a customers table containing alternative ways to contact a customer.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    primary_phone VARCHAR(20),
    secondary_phone VARCHAR(20),
    emergency_phone VARCHAR(20)
);

INSERT INTO customers
(customer_id, customer_name, primary_phone, secondary_phone, emergency_phone)
VALUES
(1, 'Aditi', '9000011111', '9000012222', '9000013333'),
(2, 'Kabir', NULL, '9000022222', '9000023333'),
(3, 'Meera', NULL, NULL, '9000033333'),
(4, 'Ishaan', NULL, NULL, NULL);

To display the first available contact number, use:

SELECT
    customer_name,
    COALESCE(
        primary_phone,
        secondary_phone,
        emergency_phone,
        'No Contact Available'
    ) AS contact_number
FROM customers;

MySQL returns the primary phone when it is available. If the primary phone is NULL, it checks the secondary phone. If both are NULL, it checks the emergency phone. If all three are NULL, it returns the fallback text.

6.5 COALESCE() with Numeric Values

COALESCE() also works with numeric expressions. For example, an online store may store an actual selling price, a promotional price, and a regular price.

SELECT
    product_name,
    COALESCE(
        promotional_price,
        regular_price,
        0
    ) AS selected_price
FROM products;

The query chooses the first non-NULL price. This is appropriate only when the column order reflects the business rule for selecting a price.

6.6 COALESCE() for Displaying Default Values

Suppose a task management system stores a task description and an optional internal note. The report should display the description when available, then the note, and finally a default message.

SELECT
    task_id,
    COALESCE(
        task_description,
        internal_note,
        'No Description Provided'
    ) AS task_information
FROM tasks;

This reduces the need to write multiple nested IFNULL() calls and makes the intended fallback order easy to understand.

7. NULLIF() Function in MySQL

7.1 What Is NULLIF()?

NULLIF() compares two expressions. If they are equal, it returns NULL. If they are not equal, it returns the first expression.

Unlike IFNULL() and COALESCE(), which can replace missing values with available alternatives, NULLIF() can turn a particular value into NULL. It is useful for treating a placeholder value as missing or for guarding calculations against a zero denominator.

7.2 Syntax of NULLIF()

NULLIF(expression1, expression2)

• expression1: The value returned when the expressions are not equal.
• expression2: The value compared with the first expression.

7.3 Simple Examples

Example 1: Both expressions are equal

SELECT NULLIF(10, 10) AS result;

Output: NULL

Example 2: Expressions are different

SELECT NULLIF(10, 5) AS result;

Output: 10

Example 3: Convert zero to NULL

SELECT NULLIF(0, 0) AS result;

Output: NULL

Example 4: Convert a placeholder text to NULL

SELECT NULLIF('Not Provided', 'Not Provided') AS email_value;

Output: NULL

7.4 Using NULLIF() to Prevent Division by Zero

Division by zero can create problems in calculations. One approach is to use NULLIF() to convert a denominator of zero into NULL.

SELECT
    100 / NULLIF(0, 0) AS result;

The denominator becomes NULL. The result of the division is NULL rather than attempting division by zero.

Consider a sales table containing revenue and units sold.

CREATE TABLE sales_summary (
    product_id INT PRIMARY KEY,
    revenue DECIMAL(10,2),
    units_sold INT
);

INSERT INTO sales_summary
(product_id, revenue, units_sold)
VALUES
(1, 5000, 100),
(2, 3000, 0),
(3, 2400, 60);

To calculate revenue per unit, use:

SELECT
    product_id,
    revenue,
    units_sold,
    revenue / NULLIF(units_sold, 0) AS revenue_per_unit
FROM sales_summary;

When units_sold is zero, NULLIF() returns NULL as the denominator and revenue_per_unit becomes NULL. This represents an undefined calculation instead of reporting a misleading numeric result.

Note: If a business report requires a numeric fallback, wrap the expression with IFNULL() or COALESCE(). Do so only when that fallback makes sense for the report.

7.5 Using NULLIF() to Clean Placeholder Values

Some systems store placeholder text such as 'N/A' or 'Not Provided' instead of storing NULL. NULLIF() can convert a known placeholder into NULL.

SELECT
    customer_name,
    NULLIF(email, 'N/A') AS cleaned_email
FROM customers;

If email equals 'N/A', the returned cleaned_email value is NULL. Otherwise, the original email value is returned.

This expression only handles the exact placeholder supplied. If a dataset uses multiple placeholder values, use a suitable CASE expression or a series of transformations. Do not convert legitimate values into NULL without checking the data rules.

8. Difference Between IFNULL(), COALESCE(), and NULLIF()

These functions are related, but their behavior is different. Choosing the right function depends on whether you want to replace NULL, select from multiple alternatives, or turn a particular value into NULL.

8.1 Quick Comparison Examples

SELECT IFNULL(NULL, 25) AS ifnull_result;
-- Result: 25

SELECT COALESCE(NULL, NULL, 25) AS coalesce_result;
-- Result: 25

SELECT NULLIF(25, 25) AS nullif_result;
-- Result: NULL

8.2 IFNULL() Versus COALESCE()

Both functions can return a fallback when an expression is NULL. IFNULL() accepts exactly two arguments, while COALESCE() can check several alternatives.

SELECT IFNULL(primary_phone, secondary_phone)
FROM customers;

SELECT COALESCE(
    primary_phone,
    secondary_phone,
    emergency_phone,
    'No Contact Available'
)
FROM customers;

The first query checks only the primary and secondary phone numbers. The second query checks three phone numbers and supplies a default message if none is available.

8.3 IFNULL() Versus NULLIF()

SELECT IFNULL(NULL, 0) AS example_one;
-- Result: 0

SELECT NULLIF(0, 0) AS example_two;
-- Result: NULL

IFNULL() replaces NULL with an alternative value. NULLIF() does the reverse in a different sense: it can transform a matching value into NULL.

9. Combining NULL Handling Functions

MySQL allows NULL-handling functions to be nested inside one another. This is useful when data needs to be cleaned, checked, and then given a default value.

9.1 Combining NULLIF() and COALESCE()

Suppose a product's price may contain NULL or zero, and zero is used as a placeholder meaning that the price has not been entered. The following expression converts zero to NULL and then selects a fallback.

SELECT
    product_name,
    COALESCE(
        NULLIF(promotional_price, 0),
        regular_price,
        0
    ) AS selected_price
FROM products;

The query first converts a promotional price of zero into NULL. COALESCE() then checks regular_price. If regular_price is also NULL, the final fallback is zero. Use this logic only if zero truly means “not provided” for the promotional price.

9.2 Combining IFNULL() and NULLIF()

A query can convert a placeholder value to NULL and then replace that NULL with a display value.

SELECT
    customer_name,
    IFNULL(
        NULLIF(email, 'N/A'),
        'Email Not Available'
    ) AS displayed_email
FROM customers;

If email is 'N/A', NULLIF() returns NULL, and IFNULL() displays 'Email Not Available'. If email is already NULL, IFNULL() also displays the fallback. Otherwise, the original email is returned.

9.3 Combining COALESCE() and NULLIF() in Calculations

When a numeric field can contain NULL or zero, the functions can help determine which value is suitable for a calculation.

SELECT
    product_id,
    COALESCE(
        revenue / NULLIF(units_sold, 0),
        0
    ) AS average_revenue_per_unit
FROM sales_summary;

This query returns zero when the calculation produces NULL, including when units_sold is zero or revenue is NULL. Use zero as the fallback only if it is appropriate for the intended report; otherwise, retaining NULL can communicate that the value is unavailable or undefined.

10. Handling NULL in SELECT Queries

NULL-handling functions are frequently used in SELECT statements to format output and produce consistent report columns.

SELECT
    employee_id,
    employee_name,
    IFNULL(bonus, 0) AS bonus,
    COALESCE(department, 'Unassigned') AS department
FROM employees;

The query returns each employee's details. Missing bonuses are displayed as zero, and a NULL department is displayed as 'Unassigned'. The original table is not modified.

11. Handling NULL in WHERE Conditions

NULL-handling functions can appear in WHERE conditions, but the correct choice depends on the desired business rule.

11.1 Find Records with Missing Values

SELECT *
FROM employees
WHERE bonus IS NULL;

This query finds employees whose bonus is missing or unknown.

11.2 Find Records with Present Values

SELECT *
FROM employees
WHERE bonus IS NOT NULL;

This query returns employees whose bonus column contains a value, including zero.

11.3 Filter After Replacing NULL

SELECT
    employee_name,
    IFNULL(bonus, 0) AS bonus_amount
FROM employees
WHERE IFNULL(bonus, 0) = 0;

This returns employees whose bonus is either NULL or zero. However, applying a function to a column in a filtering condition may affect index usage. For large tables, consider writing the equivalent condition explicitly:

SELECT
    employee_name,
    IFNULL(bonus, 0) AS bonus_amount
FROM employees
WHERE bonus IS NULL OR bonus = 0;

Use EXPLAIN to inspect query plans when performance matters.

12. NULL Handling in Aggregate Calculations

Aggregate functions such as SUM(), AVG(), MIN(), and MAX() generally ignore NULL values in the expression being aggregated. COUNT(column) counts non-NULL values, whereas COUNT(*) counts rows.

12.1 COUNT(*) Versus COUNT(column)

SELECT
    COUNT(*) AS total_employees,
    COUNT(bonus) AS employees_with_bonus_value
FROM employees;

COUNT(*) counts every employee row. COUNT(bonus) counts rows where bonus is not NULL. An employee with a bonus value of zero is included in COUNT(bonus) because zero is not NULL.

12.2 SUM() with NULL Values

SELECT
    SUM(bonus) AS total_bonus
FROM employees;

SUM(bonus) ignores individual NULL bonus values. If all bonus values are NULL, SUM(bonus) returns NULL. If the report requires zero in that situation, use:

SELECT
    IFNULL(SUM(bonus), 0) AS total_bonus
FROM employees;

This is different from replacing every row's NULL bonus before aggregation. For example, SUM(IFNULL(bonus, 0)) treats each missing bonus as zero. Choose based on the meaning of the report.

12.3 AVG() with NULL Values

SELECT
    AVG(bonus) AS average_known_bonus,
    AVG(IFNULL(bonus, 0)) AS average_with_missing_as_zero
FROM employees;

The first expression calculates the average using non-NULL bonus values only. The second treats every NULL bonus as zero before calculating the average, which can lower the result. These calculations answer different questions and should not be used interchangeably.

13. NULL Handling in JOIN Operations

NULL values may appear in query results when using outer joins. For example, a LEFT JOIN returns rows from the left table even when there is no matching row in the right table. Columns from the unmatched right-side row are returned as NULL.

SELECT
    c.customer_id,
    c.customer_name,
    COALESCE(o.order_status, 'No Order') AS order_status
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id;

For customers without a matching order, order_status is displayed as 'No Order'. If a matching order exists but its order_status is itself NULL, the fallback is also displayed. If those two cases must be distinguished, select an additional non-NULL column from the orders table, such as its primary key.

14. NULL Handling for Data Cleaning

Real-world datasets may contain inconsistent missing-value representations, including NULL, empty strings, spaces, and placeholder text. These values should be assessed before being standardized.

14.1 Detect NULL Values

SELECT *
FROM customers
WHERE email IS NULL;

14.2 Detect Empty Strings

SELECT *
FROM customers
WHERE email = '';

14.3 Detect Empty or Whitespace-Only Values

SELECT *
FROM customers
WHERE email IS NOT NULL
  AND TRIM(email) = '';

TRIM() removes leading and trailing ordinary spaces. If data contains other whitespace characters, additional cleaning may be required.

14.4 Display a Default for Empty or NULL Email Values

SELECT
    customer_name,
    COALESCE(
        NULLIF(TRIM(email), ''),
        'Email Not Available'
    ) AS displayed_email
FROM customers;

TRIM(email) removes leading and trailing spaces. NULLIF() converts an empty result to NULL. COALESCE() then returns the default text for either an original NULL value or an empty or space-only value.

14.5 Permanently Update Values When Required

IFNULL(), COALESCE(), and NULLIF() normally transform values only within the expression being evaluated. To permanently clean stored data, use an UPDATE statement only after confirming the desired business rules and taking an appropriate backup.

UPDATE customers
SET email = NULL
WHERE email IS NOT NULL
  AND TRIM(email) = '';

This example converts empty or space-only email values to NULL. It changes the stored data, unlike a SELECT query. Review the affected records before executing such an update on production data.

15. Common Mistakes and Troubleshooting

15.1 Troubleshooting Incorrect Results

If a query produces unexpected NULL values, check the source data first.

SELECT
    COUNT(*) AS total_rows,
    COUNT(bonus) AS non_null_bonuses
FROM employees;

Then test the expression independently:

SELECT
    bonus,
    IFNULL(bonus, 0) AS test_ifnull,
    COALESCE(bonus, 0, 100) AS test_coalesce,
    NULLIF(bonus, 0) AS test_nullif
FROM employees;

This helps reveal whether the input is NULL, zero, or another value and how each function transforms it.

16. Best Practices for NULL Handling

• Use IS NULL and IS NOT NULL when checking whether a value is missing.
• Use IFNULL() when exactly one fallback value is needed.
• Use COALESCE() when multiple fallback expressions must be checked in order.
• Use NULLIF() when a specific value should be converted into NULL if it matches a comparison value.
• Choose fallbacks based on business meaning rather than appearance alone.
• Do not automatically convert every missing value to zero in financial, statistical, or analytical calculations.
• Keep the distinction between unknown, not applicable, zero, and empty text clear.
• Use aliases to make calculated columns easy to understand.
• Check data types when combining numeric, text, and date expressions.
• Test NULL-handling logic on representative records before using it in production reports.
• Use EXPLAIN when a function in a WHERE clause may affect performance on a large table.
• Use transactions, backups, and carefully reviewed conditions when permanently cleaning data with UPDATE.

17. Practical Mini Project: Employee Compensation Report

Project Objective: Create an employee report that displays missing bonuses consistently, calculates total compensation, and shows a safe percentage calculation.

17.1 Create the Table

CREATE TABLE employee_compensation (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10,2),
    bonus DECIMAL(10,2),
    target_amount DECIMAL(10,2)
);

17.2 Insert Sample Data

INSERT INTO employee_compensation
(employee_id, employee_name, department, salary, bonus, target_amount)
VALUES
(1, 'Aarav', 'Sales', 45000, 5000, 100000),
(2, 'Priya', NULL, 52000, NULL, 0),
(3, 'Rohan', 'IT', 40000, 0, 80000),
(4, 'Neha', NULL, 60000, 7000, NULL);

17.3 Generate the Report

SELECT
    employee_id,
    employee_name,
    COALESCE(department, 'Unassigned') AS department_name,
    salary,
    IFNULL(bonus, 0) AS displayed_bonus,
    salary + IFNULL(bonus, 0) AS total_compensation,
    salary / NULLIF(target_amount, 0) * 100 AS salary_to_target_percent
FROM employee_compensation;

17.4 Understand the Result

• COALESCE() displays 'Unassigned' when department is NULL.
• IFNULL() displays a bonus of zero when bonus is NULL.
• The total_compensation expression treats a missing bonus as zero for this report.
• NULLIF() converts a zero target amount into NULL, avoiding division by zero in the percentage calculation.
• If target_amount is NULL, the percentage calculation also returns NULL.

Project Extension: Add a column that classifies each employee's target status using CASE. Decide how NULL targets should be reported and explain why that rule is appropriate.

18. Practice Exercises

Complete the following exercises to strengthen your understanding of NULL-handling functions.

1. Write a query that replaces a NULL phone number with 'Not Available' using IFNULL().
2. Write a query that selects the first non-NULL value from email, phone, and address using COALESCE().
3. Write a query that returns NULL when a discount is equal to zero using NULLIF().
4. Calculate total salary by adding salary to IFNULL(bonus, 0).
5. Calculate price per unit using NULLIF(quantity, 0) as the denominator.
6. Display 'Department Not Assigned' when department is NULL.
7. Convert empty email strings into NULL in query output and display 'No Email' as the fallback.
8. Compare COUNT(*) with COUNT(bonus) in the employees table.
9. Write a query that returns records where bonus is either NULL or zero.
10. Create a report that uses all three functions in meaningful expressions.

19. Practice Exercise Solutions

Solution 1: IFNULL() for phone numbers

SELECT
    customer_name,
    IFNULL(primary_phone, 'Not Available') AS phone
FROM customers;

Solution 2: COALESCE() for multiple contact fields

SELECT
    customer_name,
    COALESCE(email, primary_phone, address, 'No Details') AS contact_details
FROM customers;

Solution 3: NULLIF() for zero discounts

SELECT
    product_name,
    NULLIF(discount, 0) AS adjusted_discount
FROM products;

Solution 4: Total salary and bonus

SELECT
    employee_name,
    salary + IFNULL(bonus, 0) AS total_salary
FROM employees;

Solution 5: Price per unit

SELECT
    product_id,
    total_price / NULLIF(quantity, 0) AS price_per_unit
FROM order_items;

Solution 6: Default department label

SELECT
    employee_name,
    COALESCE(department, 'Department Not Assigned') AS department
FROM employees;

Solution 7: Empty email handling

SELECT
    customer_name,
    COALESCE(NULLIF(TRIM(email), ''), 'No Email') AS displayed_email
FROM customers;

Solution 8: Count all rows and known bonuses

SELECT
    COUNT(*) AS all_employee_rows,
    COUNT(bonus) AS rows_with_known_bonus
FROM employees;

Solution 9: Find missing or zero bonuses

SELECT *
FROM employees
WHERE bonus IS NULL OR bonus = 0;

Solution 10: Report using all three functions

SELECT
    employee_name,
    COALESCE(department, 'Unassigned') AS department_name,
    IFNULL(bonus, 0) AS displayed_bonus,
    salary / NULLIF(target_amount, 0) AS salary_to_target_ratio
FROM employee_compensation;

20. Interview Questions and Answers

Q1. What is NULL in MySQL?

NULL represents a missing, unknown, or unavailable value. It is different from zero and an empty string.

Q2. What is the purpose of IFNULL()?

IFNULL() returns the first expression when it is not NULL; otherwise, it returns the second expression.

Q3. What is the syntax of IFNULL()?

IFNULL(expression, alternative_value)

Q4. What does COALESCE() do?

COALESCE() returns the first non-NULL expression from a list of two or more expressions. If all expressions are NULL, it returns NULL.

Q5. What is the difference between IFNULL() and COALESCE()?

IFNULL() accepts exactly two arguments, while COALESCE() can accept multiple alternatives. Both can be used to provide fallback values.

Q6. What does NULLIF() return?

NULLIF() returns NULL when its two expressions are equal. Otherwise, it returns the first expression.

Q7. How can NULLIF() help prevent division by zero?

Using NULLIF(denominator, 0) converts a zero denominator into NULL. A division with that denominator returns NULL instead of attempting division by zero.

Q8. Is NULL equal to NULL in MySQL?

The expression NULL = NULL returns NULL, not TRUE. Use IS NULL to test whether a value is NULL.

Q9. Does IFNULL() permanently change data in a table?

No. When used in a SELECT expression, IFNULL() changes the query output only. A separate data modification statement is required to update stored values.

Q10. What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts rows, while COUNT(column) counts only rows where the specified column is not NULL.

Q11. Can COALESCE() return a default message?

Yes. A final non-NULL expression such as 'Not Available' can be supplied as a fallback.

Q12. Does IFNULL(column, 0) treat an empty string as NULL?

No. An empty string is not NULL. Use an expression such as NULLIF(TRIM(column), '') if empty or space-only text should be treated as missing.

Q13. Can these functions be used with numeric and text data?

Yes, but expressions should be selected carefully because MySQL determines result types based on the function's arguments and context. Mixing data types can lead to conversions or unexpected results.

Q14. When should NULL be preserved instead of replaced?

Preserve NULL when the value is genuinely unknown or undefined and replacing it with zero or text would misrepresent the information, especially in analytical and financial calculations.

Q15. Which function should be used to find the first available contact number?

COALESCE() is usually appropriate because it can check multiple contact columns in a specified order.

21. Quick Revision Checklist

• NULL represents an unknown, missing, or unavailable value.
• NULL is not the same as zero, an empty string, or whitespace.
• Use IS NULL and IS NOT NULL to check for NULL values.
• Use IFNULL() when one fallback value is needed.
• Use COALESCE() to return the first non-NULL expression from multiple alternatives.
• Use NULLIF() to return NULL when two expressions are equal.
• Use NULLIF(denominator, 0) to handle zero denominators in calculations.
• Use NULLIF(TRIM(text_column), '') when empty or space-only text should be treated as missing.
• Remember that COUNT(column) excludes NULL values, whereas COUNT(*) counts rows.
• Choose default values based on business requirements and data meaning.
• Remember that query expressions do not permanently modify stored data.
• Test queries with NULL, zero, empty-string, and normal values.

22. Conclusion

NULL handling is an essential part of writing reliable MySQL queries. IFNULL(), COALESCE(), and NULLIF() help developers manage missing information, choose alternative values, and control calculations that depend on potentially invalid or placeholder values.

Use IFNULL() when a single alternative is required, COALESCE() when several alternatives should be checked in order, and NULLIF() when matching values should become NULL. Always consider the meaning of missing data before replacing it, particularly in financial calculations, reporting, and analytics.

With regular practice, these functions can help you build cleaner reports, safer calculations, and more dependable database queries.

23. Additional Learning Resources

• Explore the MySQL Training Course
• Register for a MySQL Course Demo
• Official MySQL Documentation: IFNULL() and NULLIF()
• Official MySQL Documentation: Problems with NULL Values